package persistence

import (
	"Data-API-MCP/backend/internal/domain/entity"
	"Data-API-MCP/backend/internal/domain/repository"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"gorm.io/gorm"
)

// DBRepositoryImpl 数据库仓储实现
type DBRepositoryImpl struct {
	db *gorm.DB
}

// NewDBRepository 创建数据库仓储实例
func NewDBRepository(db *gorm.DB) repository.DBRepository {
	return &DBRepositoryImpl{
		db: db,
	}
}

// CreateConnection 创建数据库连接
func (r *DBRepositoryImpl) CreateConnection(ctx context.Context, conn *entity.DBConnection) error {
	return r.db.WithContext(ctx).Create(conn).Error
}

// GetConnections 获取数据库连接列表
func (r *DBRepositoryImpl) GetConnections(ctx context.Context, page, pageSize int, keyword string) ([]*entity.DBConnection, int64, error) {
	var conns []*entity.DBConnection
	var total int64

	// 创建查询构建器
	query := r.db.WithContext(ctx).Model(&entity.DBConnection{})

	// 添加搜索条件
	if keyword != "" {
		query = query.Where("name LIKE ? OR host LIKE ?", "%"+keyword+"%", "%"+keyword+"%")
	}

	// 获取总记录数
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	// 执行分页查询
	err := query.Offset((page - 1) * pageSize).Limit(pageSize).Find(&conns).Error
	if err != nil {
		return nil, 0, err
	}

	return conns, total, nil
}

// GetConnection 获取单个数据库连接
func (r *DBRepositoryImpl) GetConnection(ctx context.Context, id int64) (*entity.DBConnection, error) {
	var conn entity.DBConnection
	err := r.db.WithContext(ctx).First(&conn, id).Error
	return &conn, err
}

// UpdateConnection 更新数据库连接
func (r *DBRepositoryImpl) UpdateConnection(ctx context.Context, conn *entity.DBConnection) error {
	return r.db.WithContext(ctx).Save(conn).Error
}

// DeleteConnection 删除数据库连接
func (r *DBRepositoryImpl) DeleteConnection(ctx context.Context, id int64) error {
	return r.db.WithContext(ctx).Delete(&entity.DBConnection{}, id).Error
}

// TestConnection 测试数据库连接
func (r *DBRepositoryImpl) TestConnection(ctx context.Context, conn *entity.DBConnection) error {
	var err error
	// 判断id是否存在，如果存在则获取该条信息后再测试连接
	if conn.ID != 0 {
		conn, err = r.GetConnection(ctx, conn.ID)
		if err != nil {
			return err
		}
		// 解密密码
		conn.Password, err = DecryptPassword(conn.Password)
		if err != nil {
			return err
		}
	}
	// 依据数据库类型进行选择不同的测试方式
	switch conn.Type {
	case "mysql":
		dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
			conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)
		db, err := sql.Open("mysql", dsn)
		if err != nil {
			return err
		}
		defer db.Close()
		// 设置连接超时
		ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
		defer cancel()
		// 测试连接
		return db.PingContext(ctx)
	case "postgresql":
		dsn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
			conn.Host, conn.Port, conn.Username, conn.Password, conn.Database)
		db, err := sql.Open("postgres", dsn)
		if err != nil {
			return err
		}
		defer db.Close()
		// 设置连接超时
		ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
		defer cancel()
		// 测试连接
		return db.PingContext(ctx)
	default:
		return errors.New("不支持的数据库类型")
	}
}

// GetTables 获取表列表
func (r *DBRepositoryImpl) GetTables(ctx context.Context, id int64) ([]*entity.TableInfo, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			table_name,
			table_comment,
			table_rows,
			create_time,
			update_time,
			engine,
			table_collation,
			table_schema
		FROM information_schema.tables 
		WHERE table_schema = ?
	`

	rows, err := db.QueryContext(ctx, query, conn.Database)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []*entity.TableInfo
	for rows.Next() {
		var table entity.TableInfo
		err := rows.Scan(
			&table.Name,
			&table.Comment,
			&table.RowCount,
			&table.CreateTime,
			&table.UpdateTime,
			&table.Engine,
			&table.Collation,
			&table.TableSchema,
		)
		if err != nil {
			return nil, err
		}
		tables = append(tables, &table)
	}

	return tables, nil
}

// GetTableInfo 获取表信息
func (r *DBRepositoryImpl) GetTableInfo(ctx context.Context, id int64, tableName string) (*entity.TableInfo, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			table_name,
			table_comment,
			table_rows,
			create_time,
			update_time,
			engine,
			table_collation,
			table_schema
		FROM information_schema.tables 
		WHERE table_schema = ? AND table_name = ?
	`

	var table entity.TableInfo
	err = db.QueryRowContext(ctx, query, conn.Database, tableName).Scan(
		&table.Name,
		&table.Comment,
		&table.RowCount,
		&table.CreateTime,
		&table.UpdateTime,
		&table.Engine,
		&table.Collation,
		&table.TableSchema,
	)
	if err != nil {
		return nil, err
	}

	return &table, nil
}

// GetColumns 获取列信息
func (r *DBRepositoryImpl) GetColumns(ctx context.Context, id int64, tableName string) ([]*entity.ColumnInfo, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			column_name,
			column_type,
			is_nullable,
			column_default,
			column_comment,
			column_key,
			extra
		FROM information_schema.columns 
		WHERE table_schema = ? AND table_name = ?
		ORDER BY ordinal_position
	`

	rows, err := db.QueryContext(ctx, query, conn.Database, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var columns []*entity.ColumnInfo
	for rows.Next() {
		var column entity.ColumnInfo
		var isNullable, columnKey, extra string
		err := rows.Scan(
			&column.Name,
			&column.Type,
			&isNullable,
			&column.Default,
			&column.Comment,
			&columnKey,
			&extra,
		)
		if err != nil {
			return nil, err
		}

		column.Nullable = isNullable == "YES"
		column.IsPrimaryKey = columnKey == "PRI"
		column.IsUnique = columnKey == "UNI"
		column.IsIndex = columnKey != ""

		columns = append(columns, &column)
	}

	return columns, nil
}

// GetTableData 获取表数据
func (r *DBRepositoryImpl) GetTableData(ctx context.Context, id int64, tableName string, page, pageSize int) (*entity.QueryResult, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	// 获取总记录数
	var total int64
	err = db.QueryRowContext(ctx, fmt.Sprintf("SELECT COUNT(*) FROM %s", tableName)).Scan(&total)
	if err != nil {
		return nil, err
	}

	// 获取列信息
	columns, err := r.GetColumns(ctx, id, tableName)
	if err != nil {
		return nil, err
	}

	// 构建查询
	offset := (page - 1) * pageSize
	query := fmt.Sprintf("SELECT * FROM %s LIMIT %d OFFSET %d", tableName, pageSize, offset)

	// 执行查询
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 获取列名
	columnNames := make([]string, len(columns))
	for i, col := range columns {
		columnNames[i] = col.Name
	}

	// 读取数据
	var result entity.QueryResult
	result.Columns = columnNames
	result.Total = total

	// 准备接收数据的切片
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	for i := range columns {
		valuePtrs[i] = &values[i]
	}

	// 读取数据行
	for rows.Next() {
		err := rows.Scan(valuePtrs...)
		if err != nil {
			return nil, err
		}

		// 处理每一列的数据
		row := make([]interface{}, len(columns))
		for i, v := range values {
			// 处理特殊类型
			switch v.(type) {
			case []byte:
				row[i] = string(v.([]byte))
			case time.Time:
				row[i] = v.(time.Time).Format(time.RFC3339)
			default:
				row[i] = v
			}
		}
		result.Rows = append(result.Rows, row)
	}

	return &result, nil
}

// ExecuteQuery 执行查询
func (r *DBRepositoryImpl) ExecuteQuery(ctx context.Context, id int64, query string) (*entity.QueryResult, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	// 执行查询
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 获取列信息
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}

	// 准备接收数据的切片
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	for i := range columns {
		valuePtrs[i] = &values[i]
	}

	// 读取数据
	var result entity.QueryResult
	result.Columns = columns
	startTime := time.Now()

	for rows.Next() {
		err := rows.Scan(valuePtrs...)
		if err != nil {
			return nil, err
		}

		// 处理每一列的数据
		row := make([]interface{}, len(columns))
		for i, v := range values {
			// 处理特殊类型
			switch v.(type) {
			case []byte:
				row[i] = string(v.([]byte))
			case time.Time:
				row[i] = v.(time.Time).Format(time.RFC3339)
			default:
				row[i] = v
			}
		}
		result.Rows = append(result.Rows, row)
	}

	result.Total = int64(len(result.Rows))
	result.Time = float64(time.Since(startTime).Milliseconds())

	return &result, nil
}

// ExecuteTransaction 执行事务
func (r *DBRepositoryImpl) ExecuteTransaction(ctx context.Context, id int64, queries []string) error {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	// 开始事务
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	// 执行所有查询
	for _, query := range queries {
		_, err = tx.ExecContext(ctx, query)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	// 提交事务
	return tx.Commit()
}

// ExecuteBatch 执行批量操作
func (r *DBRepositoryImpl) ExecuteBatch(ctx context.Context, id int64, query string, params [][]interface{}) error {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	// 开始事务
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	// 准备语句
	stmt, err := tx.PrepareContext(ctx, query)
	if err != nil {
		tx.Rollback()
		return err
	}
	defer stmt.Close()

	// 执行批量操作
	for _, param := range params {
		_, err = stmt.ExecContext(ctx, param...)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	// 提交事务
	return tx.Commit()
}

// CreateDatabase 创建数据库
func (r *DBRepositoryImpl) CreateDatabase(ctx context.Context, id int64, name string) error {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	query := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS %s", name)
	_, err = db.ExecContext(ctx, query)
	return err
}

// DropDatabase 删除数据库
func (r *DBRepositoryImpl) DropDatabase(ctx context.Context, id int64, name string) error {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	query := fmt.Sprintf("DROP DATABASE IF EXISTS %s", name)
	_, err = db.ExecContext(ctx, query)
	return err
}

// CreateTable 创建表
func (r *DBRepositoryImpl) CreateTable(ctx context.Context, id int64, tableName string, columns []*entity.ColumnInfo) error {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	// 构建列定义
	var columnDefs []string
	for _, col := range columns {
		def := fmt.Sprintf("`%s` %s", col.Name, col.Type)
		if !col.Nullable {
			def += " NOT NULL"
		}
		if col.Default != "" {
			def += fmt.Sprintf(" DEFAULT %s", col.Default)
		}
		if col.Comment != "" {
			def += fmt.Sprintf(" COMMENT '%s'", col.Comment)
		}
		columnDefs = append(columnDefs, def)
	}

	// 构建主键
	var primaryKeys []string
	for _, col := range columns {
		if col.IsPrimaryKey {
			primaryKeys = append(primaryKeys, fmt.Sprintf("`%s`", col.Name))
		}
	}
	if len(primaryKeys) > 0 {
		columnDefs = append(columnDefs, fmt.Sprintf("PRIMARY KEY (%s)", strings.Join(primaryKeys, ", ")))
	}

	// 构建创建表语句
	query := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (\n%s\n)", tableName, strings.Join(columnDefs, ",\n"))
	_, err = db.ExecContext(ctx, query)
	return err
}

// DropTable 删除表
func (r *DBRepositoryImpl) DropTable(ctx context.Context, id int64, tableName string) error {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	query := fmt.Sprintf("DROP TABLE IF EXISTS %s", tableName)
	_, err = db.ExecContext(ctx, query)
	return err
}

// AlterTable 修改表
func (r *DBRepositoryImpl) AlterTable(ctx context.Context, id int64, tableName string, changes []string) error {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer db.Close()

	// 开始事务
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	// 执行所有修改
	for _, change := range changes {
		query := fmt.Sprintf("ALTER TABLE %s %s", tableName, change)
		_, err = tx.ExecContext(ctx, query)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	// 提交事务
	return tx.Commit()
}

// GetDatabaseStats 获取数据库统计信息
func (r *DBRepositoryImpl) GetDatabaseStats(ctx context.Context, id int64) (*entity.DatabaseStats, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	// 获取数据库大小
	var size int64
	err = db.QueryRowContext(ctx, `
		SELECT SUM(data_length + index_length)
		FROM information_schema.tables
		WHERE table_schema = ?
	`, conn.Database).Scan(&size)
	if err != nil {
		return nil, err
	}

	// 获取表数量
	var tableCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.tables
		WHERE table_schema = ?
	`, conn.Database).Scan(&tableCount)
	if err != nil {
		return nil, err
	}

	// 获取视图数量
	var viewCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.views
		WHERE table_schema = ?
	`, conn.Database).Scan(&viewCount)
	if err != nil {
		return nil, err
	}

	// 获取存储过程数量
	var procedureCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.routines
		WHERE routine_schema = ? AND routine_type = 'PROCEDURE'
	`, conn.Database).Scan(&procedureCount)
	if err != nil {
		return nil, err
	}

	// 获取函数数量
	var functionCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.routines
		WHERE routine_schema = ? AND routine_type = 'FUNCTION'
	`, conn.Database).Scan(&functionCount)
	if err != nil {
		return nil, err
	}

	// 获取触发器数量
	var triggerCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.triggers
		WHERE trigger_schema = ?
	`, conn.Database).Scan(&triggerCount)
	if err != nil {
		return nil, err
	}

	// 获取事件数量
	var eventCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.events
		WHERE event_schema = ?
	`, conn.Database).Scan(&eventCount)
	if err != nil {
		return nil, err
	}

	// 获取用户数量
	var userCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(DISTINCT user)
		FROM information_schema.processlist
		WHERE db = ?
	`, conn.Database).Scan(&userCount)
	if err != nil {
		return nil, err
	}

	// 获取连接数量
	var connectionCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.processlist
		WHERE db = ?
	`, conn.Database).Scan(&connectionCount)
	if err != nil {
		return nil, err
	}

	// 获取查询统计
	var queryCount, slowQueryCount, errorCount int64
	err = db.QueryRowContext(ctx, `
		SELECT 
			SUM(COUNT_STAR) as query_count,
			SUM(SUM_TIMER_WAIT) / 1000000000 as slow_query_count,
			SUM(SUM_ERRORS) as error_count
		FROM performance_schema.events_statements_summary_by_digest
		WHERE SCHEMA_NAME = ?
	`, conn.Database).Scan(&queryCount, &slowQueryCount, &errorCount)
	if err != nil {
		return nil, err
	}

	return &entity.DatabaseStats{
		ID:              id,
		Name:            conn.Database,
		Size:            size,
		TableCount:      tableCount,
		ViewCount:       viewCount,
		ProcedureCount:  procedureCount,
		FunctionCount:   functionCount,
		TriggerCount:    triggerCount,
		EventCount:      eventCount,
		UserCount:       userCount,
		ConnectionCount: connectionCount,
		QueryCount:      queryCount,
		SlowQueryCount:  slowQueryCount,
		ErrorCount:      errorCount,
		CreatedAt:       conn.CreatedAt,
		UpdatedAt:       conn.UpdatedAt,
	}, nil
}

// GetTableStats 获取表统计信息
func (r *DBRepositoryImpl) GetTableStats(ctx context.Context, id int64, tableName string) (*entity.TableStats, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	// 获取表大小
	var size int64
	err = db.QueryRowContext(ctx, `
		SELECT data_length + index_length
		FROM information_schema.tables
		WHERE table_schema = ? AND table_name = ?
	`, conn.Database, tableName).Scan(&size)
	if err != nil {
		return nil, err
	}

	// 获取行数
	var rowCount int64
	err = db.QueryRowContext(ctx, `
		SELECT table_rows
		FROM information_schema.tables
		WHERE table_schema = ? AND table_name = ?
	`, conn.Database, tableName).Scan(&rowCount)
	if err != nil {
		return nil, err
	}

	// 获取列数
	var columnCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.columns
		WHERE table_schema = ? AND table_name = ?
	`, conn.Database, tableName).Scan(&columnCount)
	if err != nil {
		return nil, err
	}

	// 获取索引数
	var indexCount int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.statistics
		WHERE table_schema = ? AND table_name = ?
	`, conn.Database, tableName).Scan(&indexCount)
	if err != nil {
		return nil, err
	}

	// 获取查询统计
	var queryCount, insertCount, updateCount, deleteCount int64
	err = db.QueryRowContext(ctx, `
		SELECT 
			SUM(COUNT_STAR) as query_count,
			SUM(CASE WHEN DIGEST_TEXT LIKE 'INSERT%' THEN COUNT_STAR ELSE 0 END) as insert_count,
			SUM(CASE WHEN DIGEST_TEXT LIKE 'UPDATE%' THEN COUNT_STAR ELSE 0 END) as update_count,
			SUM(CASE WHEN DIGEST_TEXT LIKE 'DELETE%' THEN COUNT_STAR ELSE 0 END) as delete_count
		FROM performance_schema.events_statements_summary_by_digest
		WHERE SCHEMA_NAME = ? AND DIGEST_TEXT LIKE ?
	`, conn.Database, "%"+tableName+"%").Scan(&queryCount, &insertCount, &updateCount, &deleteCount)
	if err != nil {
		return nil, err
	}

	// 获取最后查询时间
	var lastQueryTime time.Time
	err = db.QueryRowContext(ctx, `
		SELECT MAX(TIMER_END)
		FROM performance_schema.events_statements_summary_by_digest
		WHERE SCHEMA_NAME = ? AND DIGEST_TEXT LIKE ?
	`, conn.Database, "%"+tableName+"%").Scan(&lastQueryTime)
	if err != nil {
		return nil, err
	}

	// 获取最后更新时间
	var lastUpdateTime time.Time
	err = db.QueryRowContext(ctx, `
		SELECT update_time
		FROM information_schema.tables
		WHERE table_schema = ? AND table_name = ?
	`, conn.Database, tableName).Scan(&lastUpdateTime)
	if err != nil {
		return nil, err
	}

	return &entity.TableStats{
		ID:             id,
		Name:           tableName,
		Database:       conn.Database,
		Size:           size,
		RowCount:       rowCount,
		ColumnCount:    columnCount,
		IndexCount:     indexCount,
		QueryCount:     queryCount,
		InsertCount:    insertCount,
		UpdateCount:    updateCount,
		DeleteCount:    deleteCount,
		LastQueryTime:  lastQueryTime,
		LastUpdateTime: lastUpdateTime,
		CreatedAt:      conn.CreatedAt,
		UpdatedAt:      conn.UpdatedAt,
	}, nil
}

// GetQueryStats 获取查询统计信息
func (r *DBRepositoryImpl) GetQueryStats(ctx context.Context, id int64) ([]*entity.QueryStats, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			ID,
			SCHEMA_NAME,
			DIGEST_TEXT,
			COUNT_STAR,
			SUM_TIMER_WAIT / 1000000000 as duration,
			SUM_ROWS_AFFECTED,
			SUM_ERRORS,
			USER,
			HOST,
			TIMER_END
		FROM performance_schema.events_statements_summary_by_digest
		WHERE SCHEMA_NAME = ?
		ORDER BY SUM_TIMER_WAIT DESC
		LIMIT 100
	`

	rows, err := db.QueryContext(ctx, query, conn.Database)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stats []*entity.QueryStats
	for rows.Next() {
		var stat entity.QueryStats
		var queryType string
		err := rows.Scan(
			&stat.ID,
			&stat.Database,
			&stat.Query,
			&queryType,
			&stat.Duration,
			&stat.RowsAffected,
			&stat.Error,
			&stat.User,
			&stat.Host,
			&stat.CreatedAt,
		)
		if err != nil {
			return nil, err
		}

		// 解析查询类型
		if strings.HasPrefix(stat.Query, "SELECT") {
			stat.Type = "SELECT"
		} else if strings.HasPrefix(stat.Query, "INSERT") {
			stat.Type = "INSERT"
		} else if strings.HasPrefix(stat.Query, "UPDATE") {
			stat.Type = "UPDATE"
		} else if strings.HasPrefix(stat.Query, "DELETE") {
			stat.Type = "DELETE"
		} else {
			stat.Type = "OTHER"
		}

		// 解析涉及的表
		stat.Table = extractTableName(stat.Query)

		stats = append(stats, &stat)
	}

	return stats, nil
}

// GetConnectionStats 获取连接统计信息
func (r *DBRepositoryImpl) GetConnectionStats(ctx context.Context, id int64) (*entity.ConnectionStats, error) {
	conn, err := r.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			ID,
			USER,
			HOST,
			DB,
			COMMAND,
			TIME,
			STATE,
			INFO
		FROM information_schema.processlist
		WHERE DB = ?
		ORDER BY TIME DESC
		LIMIT 1
	`

	var stats entity.ConnectionStats
	err = db.QueryRowContext(ctx, query, conn.Database).Scan(
		&stats.ID,
		&stats.User,
		&stats.Host,
		&stats.Database,
		&stats.Command,
		&stats.Time,
		&stats.State,
		&stats.Query,
	)
	if err != nil {
		return nil, err
	}

	stats.CreatedAt = time.Now()
	stats.LastActiveAt = time.Now()

	return &stats, nil
}

// RecordQuery 记录查询统计
func (r *DBRepositoryImpl) RecordQuery(ctx context.Context, id int64, stats *entity.QueryStats) error {
	return r.db.WithContext(ctx).Create(stats).Error
}

// RecordConnection 记录连接统计
func (r *DBRepositoryImpl) RecordConnection(ctx context.Context, id int64, stats *entity.ConnectionStats) error {
	return r.db.WithContext(ctx).Create(stats).Error
}

// extractTableName 从查询语句中提取表名
func extractTableName(query string) string {
	// 简单的表名提取逻辑，实际应用中可能需要更复杂的解析
	parts := strings.Fields(query)
	for i, part := range parts {
		if strings.EqualFold(part, "FROM") || strings.EqualFold(part, "JOIN") || strings.EqualFold(part, "UPDATE") {
			if i+1 < len(parts) {
				return strings.Trim(parts[i+1], "`")
			}
		}
	}
	return ""
}
